"""
配置管理数据库操作
提供数据库连接池管理和基础CRUD操作
"""

import asyncio
import logging
from typing import Dict, List, Optional, Any, Tuple
import aiomysql


class DatabaseManager:
    """数据库管理器"""

    def __init__(self, db_config: Dict[str, Any]):
        self.db_config = db_config
        self._pool = None
        self.logger = logging.getLogger(__name__)

    async def initialize(self):
        """初始化数据库连接池"""
        try:
            self._pool = await aiomysql.create_pool(
                host=self.db_config['host'],
                port=self.db_config['port'],
                user=self.db_config['user'],
                password=self.db_config['password'],
                db=self.db_config['database'],
                charset=self.db_config.get('charset', 'utf8mb4'),
                minsize=self.db_config.get('pool_min_size', 1),
                maxsize=self.db_config.get('pool_max_size', 10),
                autocommit=self.db_config.get('autocommit', True),
                echo=self.db_config.get('echo', False)
            )
            self.logger.info("Database connection pool initialized successfully")
        except Exception as e:
            self.logger.error(f"Failed to initialize database pool: {e}")
            raise

    async def get_pool(self):
        """获取数据库连接池"""
        if self._pool is None:
            await self.initialize()
        return self._pool

    async def close(self):
        """关闭数据库连接池"""
        if self._pool:
            self._pool.close()
            await self._pool.wait_closed()
            self._pool = None
            self.logger.info("Database connection pool closed")

    async def execute_query(self, query: str, params: tuple = None) -> List[Dict]:
        """执行查询操作"""
        pool = await self.get_pool()
        async with pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cursor:
                await cursor.execute(query, params)
                return await cursor.fetchall()

    async def execute_query_one(self, query: str, params: tuple = None) -> Optional[Dict]:
        """执行查询操作并返回单条记录"""
        results = await self.execute_query(query, params)
        return results[0] if results else None

    async def execute_update(self, query: str, params: tuple = None) -> int:
        """执行更新操作"""
        pool = await self.get_pool()
        async with pool.acquire() as conn:
            async with conn.cursor() as cursor:
                await cursor.execute(query, params)
                return cursor.rowcount

    async def execute_insert(self, query: str, params: tuple = None) -> int:
        """执行插入操作并返回插入的ID"""
        pool = await self.get_pool()
        async with pool.acquire() as conn:
            async with conn.cursor() as cursor:
                await cursor.execute(query, params)
                return cursor.lastrowid

    async def execute_batch(self, query: str, params_list: List[tuple]) -> int:
        """执行批量操作"""
        pool = await self.get_pool()
        async with pool.acquire() as conn:
            async with conn.cursor() as cursor:
                await cursor.executemany(query, params_list)
                return cursor.rowcount

    async def execute_transaction(self, operations: List[Tuple[str, tuple]]) -> bool:
        """执行事务操作"""
        pool = await self.get_pool()
        async with pool.acquire() as conn:
            try:
                async with conn.cursor() as cursor:
                    await conn.begin()

                    for query, params in operations:
                        await cursor.execute(query, params)

                    await conn.commit()
                    return True

            except Exception as e:
                await conn.rollback()
                self.logger.error(f"Transaction failed: {e}")
                return False

    async def test_connection(self) -> bool:
        """测试数据库连接"""
        try:
            await self.execute_query("SELECT 1")
            return True
        except Exception as e:
            self.logger.error(f"Database connection test failed: {e}")
            return False

    async def get_table_info(self, table_name: str) -> List[Dict]:
        """获取表结构信息"""
        query = """
            SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
            ORDER BY ORDINAL_POSITION
        """
        return await self.execute_query(query, (self.db_config['database'], table_name))

    async def get_database_stats(self) -> Dict[str, Any]:
        """获取数据库统计信息"""
        stats = {}

        # 获取表统计信息
        table_stats = await self.execute_query("""
            SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA = %s
        """, (self.db_config['database'],))

        stats['tables'] = table_stats

        # 获取配置系统相关表的记录数
        config_tables = ['config_groups', 'config_items', 'config_history', 'config_backups']
        for table in config_tables:
            count_query = f"SELECT COUNT(*) as count FROM {table}"
            result = await self.execute_query_one(count_query)
            stats[f'{table}_count'] = result['count'] if result else 0

        return stats


class ConfigRepository:
    """配置数据仓库"""

    def __init__(self, db_manager: DatabaseManager):
        self.db_manager = db_manager
        self.logger = logging.getLogger(__name__)

    # 配置分组相关操作
    async def get_config_groups(self, active_only: bool = True) -> List[Dict]:
        """获取配置分组列表"""
        query = """
            SELECT id, name, display_name, description, icon, sort_order,
                   is_active, created_at, updated_at
            FROM config_groups
        """
        if active_only:
            query += " WHERE is_active = TRUE"
        query += " ORDER BY sort_order"

        return await self.db_manager.execute_query(query)

    async def get_config_group_by_id(self, group_id: int) -> Optional[Dict]:
        """根据ID获取配置分组"""
        query = """
            SELECT * FROM config_groups WHERE id = %s
        """
        return await self.db_manager.execute_query_one(query, (group_id,))

    async def get_config_group_by_name(self, name: str) -> Optional[Dict]:
        """根据名称获取配置分组"""
        query = """
            SELECT * FROM config_groups WHERE name = %s
        """
        return await self.db_manager.execute_query_one(query, (name,))

    async def create_config_group(self, group_data: Dict) -> int:
        """创建配置分组"""
        query = """
            INSERT INTO config_groups (
                name, display_name, description, icon, sort_order, is_active
            ) VALUES (%s, %s, %s, %s, %s, %s)
        """
        return await self.db_manager.execute_insert(query, (
            group_data['name'],
            group_data['display_name'],
            group_data.get('description'),
            group_data.get('icon'),
            group_data.get('sort_order', 0),
            group_data.get('is_active', True)
        ))

    async def update_config_group(self, group_id: int, group_data: Dict) -> bool:
        """更新配置分组"""
        # 动态构建更新语句
        set_clauses = []
        params = []

        for field in ['display_name', 'description', 'icon', 'sort_order', 'is_active']:
            if field in group_data:
                set_clauses.append(f"{field} = %s")
                params.append(group_data[field])

        if not set_clauses:
            return False

        set_clauses.append("updated_at = CURRENT_TIMESTAMP")
        query = f"UPDATE config_groups SET {', '.join(set_clauses)} WHERE id = %s"
        params.append(group_id)

        return await self.db_manager.execute_update(query, tuple(params)) > 0

    async def delete_config_group(self, group_id: int) -> bool:
        """删除配置分组（软删除）"""
        query = "UPDATE config_groups SET is_active = FALSE WHERE id = %s"
        return await self.db_manager.execute_update(query, (group_id,)) > 0

    # 配置项相关操作
    async def get_config_items(self, group_id: Optional[int] = None,
                              category: Optional[str] = None,
                              search: Optional[str] = None,
                              page: int = 1,
                              page_size: int = 50) -> Tuple[List[Dict], int]:
        """获取配置项列表"""
        # 构建查询条件
        where_conditions = ["g.is_active = TRUE"]
        params = []

        if group_id:
            where_conditions.append("i.group_id = %s")
            params.append(group_id)

        if category:
            where_conditions.append("i.category = %s")
            params.append(category)

        if search:
            where_conditions.append("""
                (i.key_name LIKE %s OR i.display_name LIKE %s OR i.description LIKE %s)
            """)
            search_param = f"%{search}%"
            params.extend([search_param, search_param, search_param])

        where_clause = "WHERE " + " AND ".join(where_conditions) if where_conditions else ""

        # 获取总数
        count_query = f"""
            SELECT COUNT(*) as total
            FROM config_items i
            JOIN config_groups g ON i.group_id = g.id
            {where_clause}
        """
        total_result = await self.db_manager.execute_query_one(count_query, tuple(params))
        total = total_result['total'] if total_result else 0

        # 获取分页数据
        offset = (page - 1) * page_size
        query = f"""
            SELECT i.*, g.name as group_name
            FROM config_items i
            JOIN config_groups g ON i.group_id = g.id
            {where_clause}
            ORDER BY g.sort_order, i.sort_order
            LIMIT %s OFFSET %s
        """
        params.extend([page_size, offset])

        items = await self.db_manager.execute_query(query, tuple(params))

        return items, total

    async def get_config_item_by_id(self, item_id: int) -> Optional[Dict]:
        """根据ID获取配置项"""
        query = """
            SELECT i.*, g.name as group_name
            FROM config_items i
            JOIN config_groups g ON i.group_id = g.id
            WHERE i.id = %s
        """
        return await self.db_manager.execute_query_one(query, (item_id,))

    async def get_config_item_by_key(self, key: str) -> Optional[Dict]:
        """根据键名获取配置项"""
        query = """
            SELECT i.*, g.name as group_name
            FROM config_items i
            JOIN config_groups g ON i.group_id = g.id
            WHERE i.key_name = %s
        """
        return await self.db_manager.execute_query_one(query, (key,))

    async def create_config_item(self, item_data: Dict) -> int:
        """创建配置项"""
        query = """
            INSERT INTO config_items (
                group_id, category, key_name, display_name, description,
                data_type, default_value, current_value, value_range,
                validation_rule, is_sensitive, is_reload_required,
                is_readonly, placeholder, sort_order
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        return await self.db_manager.execute_insert(query, (
            item_data['group_id'],
            item_data['category'],
            item_data['key_name'],
            item_data['display_name'],
            item_data.get('description'),
            item_data['data_type'],
            item_data.get('default_value'),
            item_data.get('current_value'),
            item_data.get('value_range'),
            item_data.get('validation_rule'),
            item_data.get('is_sensitive', False),
            item_data.get('is_reload_required', True),
            item_data.get('is_readonly', False),
            item_data.get('placeholder'),
            item_data.get('sort_order', 0)
        ))

    async def update_config_item(self, item_id: int, item_data: Dict) -> bool:
        """更新配置项"""
        set_clauses = []
        params = []

        for field in ['display_name', 'description', 'current_value', 'value_range',
                     'validation_rule', 'is_sensitive', 'is_reload_required',
                     'is_readonly', 'placeholder', 'sort_order']:
            if field in item_data:
                set_clauses.append(f"{field} = %s")
                params.append(item_data[field])

        if not set_clauses:
            return False

        set_clauses.append("updated_at = CURRENT_TIMESTAMP")
        query = f"UPDATE config_items SET {', '.join(set_clauses)} WHERE id = %s"
        params.append(item_id)

        return await self.db_manager.execute_update(query, tuple(params)) > 0

    async def update_config_value(self, key: str, value: str) -> bool:
        """更新配置值"""
        query = """
            UPDATE config_items
            SET current_value = %s, updated_at = CURRENT_TIMESTAMP
            WHERE key_name = %s
        """
        return await self.db_manager.execute_update(query, (value, key)) > 0

    async def delete_config_item(self, item_id: int) -> bool:
        """删除配置项"""
        query = "DELETE FROM config_items WHERE id = %s"
        return await self.db_manager.execute_update(query, (item_id,)) > 0

    # 配置历史相关操作
    async def create_config_history(self, history_data: Dict) -> int:
        """创建配置历史记录"""
        query = """
            INSERT INTO config_history (
                config_key, config_id, old_value, new_value,
                change_type, changed_by, change_reason, ip_address, user_agent
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        return await self.db_manager.execute_insert(query, (
            history_data['config_key'],
            history_data['config_id'],
            history_data.get('old_value'),
            history_data.get('new_value'),
            history_data['change_type'],
            history_data.get('changed_by'),
            history_data.get('change_reason'),
            history_data.get('ip_address'),
            history_data.get('user_agent')
        ))

    async def get_config_history(self, config_key: Optional[str] = None,
                                change_type: Optional[str] = None,
                                limit: int = 50,
                                offset: int = 0) -> List[Dict]:
        """获取配置历史记录"""
        where_conditions = []
        params = []

        if config_key:
            where_conditions.append("h.config_key = %s")
            params.append(config_key)

        if change_type:
            where_conditions.append("h.change_type = %s")
            params.append(change_type)

        where_clause = "WHERE " + " AND ".join(where_conditions) if where_conditions else ""

        query = f"""
            SELECT h.*, i.display_name
            FROM config_history h
            LEFT JOIN config_items i ON h.config_id = i.id
            {where_clause}
            ORDER BY h.created_at DESC
            LIMIT %s OFFSET %s
        """
        params.extend([limit, offset])

        return await self.db_manager.execute_query(query, tuple(params))

    async def get_config_history_count(self, config_key: Optional[str] = None,
                                     change_type: Optional[str] = None) -> int:
        """获取配置历史记录总数"""
        where_conditions = []
        params = []

        if config_key:
            where_conditions.append("config_key = %s")
            params.append(config_key)

        if change_type:
            where_conditions.append("change_type = %s")
            params.append(change_type)

        where_clause = "WHERE " + " AND ".join(where_conditions) if where_conditions else ""

        query = f"SELECT COUNT(*) as total FROM config_history {where_clause}"
        result = await self.db_manager.execute_query_one(query, tuple(params))
        return result['total'] if result else 0

    # 配置备份相关操作
    async def create_config_backup(self, backup_data: Dict) -> int:
        """创建配置备份"""
        query = """
            INSERT INTO config_backups (
                backup_name, backup_description, backup_data, backup_version,
                is_auto_backup, created_by, file_size, checksum
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """
        return await self.db_manager.execute_insert(query, (
            backup_data['backup_name'],
            backup_data.get('backup_description'),
            backup_data['backup_data'],
            backup_data.get('backup_version', '1.0'),
            backup_data.get('is_auto_backup', False),
            backup_data.get('created_by'),
            backup_data.get('file_size'),
            backup_data.get('checksum')
        ))

    async def get_config_backup(self, backup_id: int) -> Optional[Dict]:
        """获取配置备份"""
        query = "SELECT * FROM config_backups WHERE id = %s"
        return await self.db_manager.execute_query_one(query, (backup_id,))

    async def get_config_backups(self, limit: int = 50, offset: int = 0) -> List[Dict]:
        """获取配置备份列表"""
        query = """
            SELECT id, backup_name, backup_description, created_by,
                   created_at, file_size, is_auto_backup, backup_version
            FROM config_backups
            ORDER BY created_at DESC
            LIMIT %s OFFSET %s
        """
        return await self.db_manager.execute_query(query, (limit, offset))

    async def delete_config_backup(self, backup_id: int) -> bool:
        """删除配置备份"""
        query = "DELETE FROM config_backups WHERE id = %s"
        return await self.db_manager.execute_update(query, (backup_id,)) > 0

    # 统计和状态相关操作
    async def get_config_statistics(self) -> Dict[str, Any]:
        """获取配置统计信息"""
        stats = {}

        # 获取配置项统计
        item_stats = await self.db_manager.execute_query("""
            SELECT
                COUNT(*) as total,
                SUM(CASE WHEN is_sensitive = TRUE THEN 1 ELSE 0 END) as sensitive_count,
                SUM(CASE WHEN is_reload_required = TRUE THEN 1 ELSE 0 END) as reload_required_count,
                MAX(i.updated_at) as last_update
            FROM config_items i
            JOIN config_groups g ON i.group_id = g.id
            WHERE g.is_active = TRUE
        """)

        if item_stats:
            stats.update(item_stats[0])

        # 获取分组统计
        group_stats = await self.db_manager.execute_query("""
            SELECT COUNT(*) as group_count
            FROM config_groups
            WHERE is_active = TRUE
        """)

        if group_stats:
            stats['config_groups'] = group_stats[0]['group_count']

        # 获取历史记录统计
        history_stats = await self.db_manager.execute_query("""
            SELECT
                COUNT(*) as total_changes,
                COUNT(DISTINCT config_key) as changed_configs,
                MAX(created_at) as last_change
            FROM config_history
        """)

        if history_stats:
            stats.update(history_stats[0])

        # 获取备份统计
        backup_stats = await self.db_manager.execute_query("""
            SELECT
                COUNT(*) as total_backups,
                SUM(CASE WHEN is_auto_backup = TRUE THEN 1 ELSE 0 END) as auto_backups,
                MAX(created_at) as last_backup
            FROM config_backups
        """)

        if backup_stats:
            stats.update(backup_stats[0])

        return stats